package tgc.edu.tms.web.controller;

import java.io.IOException;
import java.io.OutputStream;
import java.math.BigInteger;
import java.sql.Date;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;

import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.util.Base64Utils;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import tgc.edu.tms.custom.AjaxResult;
import tgc.edu.tms.custom.CommonController;
import tgc.edu.tms.custom.ExportExcelUtils;
import tgc.edu.tms.custom.TablePageable;
import tgc.edu.tms.custom.TimeUtils;
import tgc.edu.tms.entity.Attendance;
import tgc.edu.tms.entity.Callnames;
import tgc.edu.tms.entity.FlowTable;
import tgc.edu.tms.entity.Schedule;
import tgc.edu.tms.entity.Student;
import tgc.edu.tms.entity.WorkSheetCount;
import tgc.edu.tms.security.UserUtils;
import tgc.edu.tms.service.CallnamesService;
import tgc.edu.tms.service.ScheduleService;
import tgc.edu.tms.service.StudentService;
import tgc.edu.tms.web.form.CallnamesForm;
import tgc.edu.tms.web.form.ScheduleForm;

@Controller
@RequestMapping(value = "callnames")
public class AttendanceController {
	@Autowired
	private UserUtils userUtils;
	@Autowired
	private ScheduleService scheduleService;
	@Autowired
	private CallnamesService callnamesService;

	@Autowired
	private StudentService studentService;

	/***
	 * 出勤率
	 * 
	 * @param map
	 * @return
	 */
	@RequestMapping(value = "/attendance")
	public String attendance(ModelMap map) {
		String name = userUtils.getUser().getName();
		String xq = TimeUtils.getxq();
		List<String> course = scheduleService.findAllKcmc(name, xq);
		map.put("course", course);
		return "callnames/attendance";
	}

	/***
	 * 课程发生变化时加载班级
	 * 
	 * @param kcmc 课程名称
	 * @return
	 */
	@RequestMapping(value = "/kcmcchange")
	@ResponseBody
	public Object kcmcchange(String kcmc) {
		String name = userUtils.getUser().getName();
		List<Schedule> banji = scheduleService.findByTeacherAndKcmc(name, kcmc);
		return banji;
	}

	@RequestMapping(value = "/page1")
	@ResponseBody
	public Object page(TablePageable pageParam, CallnamesForm form, String overtime,HttpSession session) {
		String banji1 = form.getBanji();
		String time = form.getTime();
		String kc = form.getKc();
		List<Integer> studentid = callnamesService.findAllStudent(banji1);
		List<Object[]> attendance = null;
		List<Object[]> sdcs = null;
		List<Attendance> attlist = new ArrayList<>();
		for (Integer student : studentid) {
			Student studentname = studentService.findById(student);
			attendance = callnamesService.findAttendance(kc, time, overtime, student);
			// 实到次数
			sdcs = callnamesService.findAllSdcs(kc, time, overtime, student);
			Double sdcssize = (double) sdcs.size();
			Double ydcssize = (double) attendance.size();
			Double cql1 = (double) ((sdcssize / ydcssize) * 100);
			DecimalFormat df = new DecimalFormat("#.00");
			String str = df.format(cql1) + "%";
			Attendance att = new Attendance(kc, studentname.getName(), studentname.getTbclass(), attendance.size(),
					sdcs.size(), str);
			attlist.add(att);
		}
		session.setAttribute("form", form);
		session.setAttribute("overtime", overtime);
		
		return attlist;
	}

	@RequestMapping(value = "/export")
	@ResponseBody
	public void export(TablePageable pageParam, HttpServletRequest request, HttpServletResponse response,HttpSession session) {
		CallnamesForm form = (CallnamesForm) session.getAttribute("form");
		String overtime = (String) session.getAttribute("overtime");
		String banji1 = form.getBanji();
		String time = form.getTime();
		String kc = form.getKc();
		List<Integer> studentid = callnamesService.findAllStudent(banji1);
		List<Object[]> attendance = null;
		List<Object[]> sdcs = null;
		List<Attendance> attlist = new ArrayList<>();
		String title = "出勤率统计表";
		String[] columnName = new String[] { "序号", "学生", "班级", "课程", "应到次数", "实到次数", "出勤率" };
		ExportExcelUtils excel = new ExportExcelUtils();
		
		try {
			HSSFWorkbook workbook = new HSSFWorkbook(); // 创建工作簿对象
			HSSFSheet sheet = workbook.createSheet(title); // 创建工作表

			// 产生表格标题行
			HSSFRow rowm = sheet.createRow(0);
			HSSFCell cellTiltle = rowm.createCell(0);

			// 设置标题和单元格样式
			HSSFCellStyle columnTopStyle = excel.getColumnTopStyle(workbook); // 获取列头样式对象
			HSSFCellStyle style = excel.getStyle(workbook); // 单元格样式对象

			// 合并单元格
			sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (columnName.length - 1)));
			cellTiltle.setCellStyle(columnTopStyle);
			cellTiltle.setCellValue(title);

			// 定义所需列数
			int columnNum = columnName.length;
			HSSFRow rowRowName = sheet.createRow(2); // 在索引2的位置创建行(最顶端的行开始的第二行)

			// 将列头设置到sheet的单元格中
			for (int n = 0; n < columnNum; n++) {
				HSSFCell cellRowName = rowRowName.createCell(n); // 创建列头对应个数的单元格
				cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING); // 设置列头单元格的数据类型
				HSSFRichTextString text = new HSSFRichTextString(columnName[n]);
				cellRowName.setCellValue(text); // 设置列头单元格的值
				cellRowName.setCellStyle(columnTopStyle); // 设置列头单元格样式
			}

			// 将查询出的数据设置到sheet对应的单元格中
			int a = 0, m = 3;
			for (Integer student : studentid) {
				Student studentname = studentService.findById(student);
				attendance = callnamesService.findAttendance(kc, time, overtime, student);
				// 实到次数
				sdcs = callnamesService.findAllSdcs(kc, time, overtime, student);
				Double sdcssize = (double) sdcs.size();
				Double ydcssize = (double) attendance.size();
				Double cql1 = (double) ((sdcssize / ydcssize) * 100);
				DecimalFormat df = new DecimalFormat("#.00");
				String str = df.format(cql1) + "%";
				Attendance att = new Attendance(kc, studentname.getName(), studentname.getTbclass(), attendance.size(),
						sdcs.size(), str);
				
				HSSFRow row = sheet.createRow(a + 3);// 创建所需的行数

				HSSFCell cell = null; // 设置单元格的数据类型
				// 第一列为数字类型并设置单元格的值

				cell = row.createCell(0, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellValue(a + 1);
				cell.setCellStyle(style);
				// 其他列为字符串类型并设置单元格的值
				cell = row.createCell(1, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(att.getStudent());
				cell.setCellStyle(style);
				cell = row.createCell(2, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(att.getBanji());
				cell.setCellStyle(style);
				cell = row.createCell(3, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(att.getKc());
				cell.setCellStyle(style);
				cell = row.createCell(4, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(att.getYdcs());
				cell.setCellStyle(style);
				cell = row.createCell(5, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(att.getSdcs());
				cell.setCellStyle(style);
				cell = row.createCell(6, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(att.getCql());
				cell.setCellStyle(style);
				a+=1;
			}

			// 让列宽随着导出的列长自动适应
			for (int colNum = 0; colNum < columnNum; colNum++) {
				int columnWidth = sheet.getColumnWidth(colNum) / 256;
				for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
					HSSFRow currentRow;
					// 当前行未被使用过
					if (sheet.getRow(rowNum) == null) {
						currentRow = sheet.createRow(rowNum);
					} else {
						currentRow = sheet.getRow(rowNum);
					}
					if (currentRow.getCell(colNum) != null) {
						// 取得当前的单元格
						HSSFCell currentCell = currentRow.getCell(colNum);
						// 如果当前单元格类型为字符串
						if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
							int length = currentCell.getStringCellValue().getBytes().length;
							if (columnWidth < length) {
								// 将单元格里面值大小作为列宽度
								columnWidth = length;
							}
						}
					}
				}
				// 再根据不同列单独做下处理
				if (colNum == 0) {
					sheet.setColumnWidth(colNum, (columnWidth - 2) * 256);
				} else {
					sheet.setColumnWidth(colNum, (columnWidth + 4) * 256);
				}
			}

			if (workbook != null) {
				try {
					String agent = request.getHeader("USER-AGENT");
					String fileName = "Excel-" + time +" -- "+overtime+ "工作量统计表.xls";
					if (agent != null && agent.toLowerCase().indexOf("firefox") > 0) {
						fileName = "=?UTF-8?B?" + Base64Utils.encodeToString(fileName.getBytes("UTF-8")) + "?=";
					}
					String headStr = "attachment; filename=\"" + fileName + "\"";
					response.setContentType("APPLICATION/OCTET-STREAM");
					response.setHeader("Content-Disposition", headStr);
					OutputStream out1 = response.getOutputStream();
					workbook.write(out1);
					workbook.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}
